package com.ciner.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class CinerDbAdapter {

	// Keys
	public static final String KEY_ROWID = "_id";

	// Movie related keys
	public static final String KEY_YEAR = "year";
	public static final String KEY_GENRE = "genre";
	public static final String KEY_TITLE = "title";
	public static final String KEY_RATING = "rating";
	public static final String KEY_DURATION = "duration";
	public static final String KEY_DESCRIPTION = "description";
	public static final String KEY_IMAGE = "image";

	// Watched movie related keys
	public static final String KEY_MOVIE = "movie";
	public static final String KEY_WATCHED_ON = "watched_at";
	public static final String KEY_MY_RATING = "my_rating";

	// Actor related keys
	public static final String KEY_NAME = "name";
	public static final String KEY_BORN = "born";

	// Starring related keys
	public static final String KEY_ACTOR = "actor";

	private static final String TAG = "CinerDbAdapter";
	private DatabaseHelper mDbHelper;
	private SQLiteDatabase mDb;

	// Database properties
	private static final String DATABASE_NAME = "cinerdb";
	private static final String DATABASE_TABLE_MOVIES = "Movies";
	private static final String DATABASE_TABLE_WATCHED_MOVIES = "Watchedmovies";
	private static final String DATABASE_TABLE_ACTORS = "Actors";
	private static final String DATABASE_TABLE_STARRING = "Starring";
	private static final int DATABASE_VERSION = 17;

	// Tables
	private static final String CREATE_TABLE_MOVIES = "create table "
			+ DATABASE_TABLE_MOVIES + " (" + KEY_ROWID
			+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + KEY_TITLE
			+ " TEXT NOT NULL, " + KEY_GENRE + " TEXT, " + KEY_DURATION
			+ " INTEGER, " + KEY_DESCRIPTION + " TEXT, " + KEY_YEAR
			+ " INTEGER, " + KEY_RATING + " INTEGER, " + KEY_IMAGE + " TEXT);";

	private static final String CREATE_TABLE_WATCHED_MOVIES = "create table "
			+ DATABASE_TABLE_WATCHED_MOVIES + " (" + KEY_MOVIE + " PRIMARY KEY"
			+ ", " + KEY_WATCHED_ON + " DATETIME NOT NULL, " + KEY_MY_RATING
			+ " INTEGER, FOREIGN KEY(Movie) REFERENCES "
			+ DATABASE_TABLE_MOVIES + "(" + KEY_ROWID + "));";

	private static final String CREATE_TABLE_ACTORS = "create table "
			+ DATABASE_TABLE_ACTORS + " (" + KEY_ROWID
			+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + KEY_NAME
			+ " TEXT NOT NULL, " + KEY_BORN + " DATETIME NOT NULL);";

	private static final String CREATE_TABLE_STARRING =
			"create table "
					+ DATABASE_TABLE_STARRING
					+ " ("
					+ KEY_MOVIE
					+ " INTEGER PRIMARY KEY, "
					+ KEY_ACTOR
					+ " INTEGER, FOREIGN KEY(Movie) REFERENCES Movies(_id), FOREIGN KEY(Actor) REFERENCES Actors(_id));";

	private final Context mCtx;

	private static class DatabaseHelper extends SQLiteOpenHelper {

		DatabaseHelper(final Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(final SQLiteDatabase db) {

			db.execSQL(CREATE_TABLE_MOVIES);
			db.execSQL(CREATE_TABLE_WATCHED_MOVIES);
			db.execSQL(CREATE_TABLE_ACTORS);
			db.execSQL(CREATE_TABLE_STARRING);

		}

		@Override
		public void onUpgrade(final SQLiteDatabase db, final int oldVersion,
				final int newVersion) {
			Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
					+ newVersion + ", which will destroy all old data");
			db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE_MOVIES);
			db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE_WATCHED_MOVIES);
			db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE_ACTORS);
			db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE_STARRING);
			onCreate(db);

		}
	}

	public CinerDbAdapter(final Context ctx) {
		mCtx = ctx;
	}

	/**
	 * Opens the database
	 * 
	 * @return
	 * @throws SQLException
	 */
	public CinerDbAdapter open() throws SQLException {
		mDbHelper = new DatabaseHelper(mCtx);
		mDb = mDbHelper.getWritableDatabase();

		return this;
	}

	/**
	 * Closes the database.
	 */
	public void close() {
		mDbHelper.close();
	}

	// CREATE

	/**
	 * Creates a new Movie entry in the Movies database with the values set
	 * according to the parameters
	 * 
	 * @param title
	 *            the title of the movie
	 * @param description
	 *            a descriptive text for the movie
	 * @param duration
	 *            the duration of the movie
	 * @param year
	 *            the production year for the movie
	 * @param rating
	 *            the IMDB rating for the movie
	 * @param imagePath
	 *            the path to the cover image for the movie
	 * @return
	 */
	public long createMovie(final String title, final String description,
			final int duration, final int year, final int rating,
			final String imagePath) {
		final ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_TITLE, title);
		initialValues.put(KEY_DURATION, duration);
		initialValues.put(KEY_DESCRIPTION, description);
		initialValues.put(KEY_YEAR, year);
		initialValues.put(KEY_RATING, rating);
		initialValues.put(KEY_IMAGE, imagePath);

		return mDb.insert(DATABASE_TABLE_MOVIES, null, initialValues);
	}

	/**
	 * Creates a new WatchedMovie entry in the WatchedMovies database with the
	 * values set according to the parameters
	 * 
	 * @param watchedOn
	 *            the date at which the movie was watched (in milliseconds since
	 *            epoch)
	 * @param myRating
	 *            the rating for the movie
	 * @return
	 */
	public long createWatchedMovie(final long watchedOn, final int myRating) {
		final ContentValues initialValues = new ContentValues();

		initialValues.put(KEY_WATCHED_ON, watchedOn);
		initialValues.put(KEY_MY_RATING, myRating);

		return mDb.insert(DATABASE_TABLE_WATCHED_MOVIES, null, initialValues);
	}

	/**
	 * Creates a new Actor entry in the Actors database with the values set
	 * according to the parameters
	 * 
	 * @param name
	 *            the name of the actor
	 * @param born
	 *            the year the actor was born
	 * @param imagePath
	 *            the path to a picture of the actor
	 */
	public long createActor(final String name, final String born,
			final String imagePath) {

		final ContentValues initialValues = new ContentValues();

		initialValues.put(KEY_NAME, name);
		initialValues.put(KEY_BORN, born);
		initialValues.put(KEY_IMAGE, imagePath);

		return mDb.insert(DATABASE_TABLE_ACTORS, null, initialValues);
	}

	/**
	 * Creates a new Starring entry in the Starring database with the values set
	 * according to the parameters
	 * 
	 * @param movie
	 *            the id of the movie
	 * @param actor
	 *            the id of the actor
	 */
	public long createStarring(int movie, int actor) {

		final ContentValues initialValues = new ContentValues();

		initialValues.put(KEY_MOVIE, movie);
		initialValues.put(KEY_ACTOR, actor);

		return mDb.insert(DATABASE_TABLE_ACTORS, null, initialValues);
	}

	public Cursor fetchAllMovies() {
		return mDb.query(DATABASE_TABLE_MOVIES, new String[] { KEY_ROWID,
				KEY_TITLE, KEY_YEAR, KEY_RATING, KEY_DURATION, KEY_GENRE,
				KEY_DESCRIPTION, KEY_IMAGE }, null, null, null, null, null);
	}

}
